/****************************************************************************************************************
* Copyright � Mathematica Policy Research, Inc.
* This code cannot be copied, distributed or used without the express written permission
* of Mathematica Policy Research, Inc.
   Program	   				: 01_Create_Bene_Analytic_File.sas
   Purpose     				: Aggregagtes claim level measures to bene level in a given year.
							  Combines all bene level measures in a given year.
							  Merges on finder file and creates dummies for whether bene had any claims in certain categories.
   Programmer  				: Claire Erba
   Researcher  				: Michael Levere
   Specificatn 				: N/A
   Project Name				: SSI Medicaid
   Project Number			: 055827
   Date Created				: 01/30/2023
   Runtime     				: [TBD]
   QA          				: Michael Levere
   Input files 				: 01-08 Datasets  
   Output files				: analytic_file_[YYYY]

   Modifications or Updates	: 01/30/2023 - New program.
							  02/13/2023 - CErba added INCM_CD to list of variables to keep from eligibility file.
										   Updated aggregation of claims module variables.
							  03/06/2023 - CErba updated to output QA to an Excel file.
*********************************************************************************************************************/
*====================================================================*;        
* Set Up															 *;
*====================================================================*;
/* Options */
options mprint symbolgen center;
options ls=154 ps=63 formchar="�----�+�---+=�-/\<>*" msglevel=I;
options mergenoby=warn dkrocond=warn dkricond=warn compress=no;
options varinitchk=warn dtreset source;
options validvarname=v7;

/* Set overall paths and libraries */
%let run_dir = [ENTER PATH]/analytic_file_for_ssa;
x "mkdir -p &run_dir./_intermediate/Analytic_File__01";
libname permwork "&run_dir./_intermediate/Analytic_File__01";
options user = permwork minoperator; 

/* Obtain starting time */
%let timer_start = %sysfunc(datetime());
data _null_;
	now = &timer_start.;
	put "Timer starting at " now dateampm.;
run;

/* Setup overall macros */
%let enroll_vars 		= 	MSIS_ID BENE_ID SUBMTG_STATE_CD BIRTH_DT DEATH_DT SEX_CD BENE_ZIP_CD BENE_CNTY_CD BENE_STATE_CD DA_RUN_ID RFRNC_YR AGE_NUM AGE_GROUP_1 
				   			CHIP_CD_01 CHIP_CD_02 CHIP_CD_03 CHIP_CD_04 CHIP_CD_05 CHIP_CD_06 CHIP_CD_07 CHIP_CD_08 CHIP_CD_09 CHIP_CD_10 CHIP_CD_11 CHIP_CD_12 
				   			PROGRAM_TYPE BENEFIT_PACKAGE DUAL_STATUS ELGBLTY_GRP_CD_01 ELGBLTY_GRP_CD_02 ELGBLTY_GRP_CD_03 ELGBLTY_GRP_CD_04 ELGBLTY_GRP_CD_05 ELGBLTY_GRP_CD_06 
                   			ELGBLTY_GRP_CD_07 ELGBLTY_GRP_CD_08 ELGBLTY_GRP_CD_09 ELGBLTY_GRP_CD_10 ELGBLTY_GRP_CD_11 ELGBLTY_GRP_CD_12 ELIGIBILITY_GROUP CMC PCCM MLTSS BHO 
				   			NONMED MEDPRE MC_OTHER CONT_ENROLL_RBC CONT_ENROLL_STRICT RACE_ETHNICITY URBAN_RURAL MFP PACE WVR_1915_C WVR_1915_I WVR_1915_J WVR_1915_K DIS_ELIG SSI_ID
							INCM_CD;
%let comorbid_flags 	= 	CMR_AIDS CMR_ALCOHOL CMR_ANEMDEF CMR_AUTOIMMUNE CMR_BLDLOSS CMR_CANCER_LEUK CMR_CANCER_LYMPH CMR_CANCER_METS CMR_CANCER_NSITU CMR_CANCER_SOLID 
					  		CMR_CBVD CMR_COAG CMR_DEMENTIA CMR_DEPRESS CMR_DIAB_CX CMR_DIAB_UNCX CMR_DRUG_ABUSE CMR_HF CMR_HTN_CX CMR_HTN_UNCX CMR_LIVER_MLD CMR_LIVER_SEV 
					  		CMR_LUNG_CHRONIC CMR_NEURO_MOVT CMR_NEURO_OTH CMR_NEURO_SEIZ CMR_OBESE CMR_PARALYSIS CMR_PERIVASC CMR_PSYCHOSES CMR_PULMCIRC CMR_RENLFL_MOD 
					  		CMR_RENLFL_SEV CMR_THYROID_HYPO CMR_THYROID_OTH CMR_ULCER_PEPTIC CMR_VALVE CMR_WGHTLOSS;
%let ccw_flags 			= 	BENE_CAT BENE_AMI BENE_STRK BENE_AFIB BENE_HFRAC BENE_CHF BENE_CRC BENE_ENDC BENE_BRC BENE_LNGC BENE_PRC BENE_HYPOTH BENE_ASTHMA BENE_HYPERP BENE_CKD 
				 			BENE_COPD BENE_DIAB BENE_HYPERL BENE_HYPERT BENE_OST BENE_ALZ BENE_ALZRDSD BENE_ANEMIA BENE_IHD BENE_RAOA BENE_GLCM BENE_EPILEP BENE_FIBRO BENE_HIVAIDS 
				 			BENE_INTDIS BENE_LEADIS BENE_LEUKLYMP BENE_MIGRAINE BENE_OBESITY BENE_PVD BENE_ULCERS BENE_HEPVIRAL BENE_LIVER BENE_AUTISM BENE_CERPAL BENE_CYSFIB 
				 			BENE_MOBIMP BENE_MULSCL BENE_MUSDYS BENE_OTHDEPL BENE_VISUAL BENE_HEARIM BENE_SPIBIF BENE_SPINJ BENE_BRAINJ BENE_SKLC BENE_TUD;
%let bh_flags 			= 	ANXIETY_DIS_FLAG CC_ADHD CC_ADHD_C CC_ANXIETY CC_ANXIETY_C CC_AUD CC_BEHAV_C CC_BH CC_BIPOLAR_DIS CC_DEPR_C CC_DEPRESSION CC_DEPRESSIVE_DIS CC_DUD 
							CC_MH CC_MOOD_C CC_OMH_C CC_OTHER_MH CC_OUD CC_PERSONALITY_DIS CC_PSY_C CC_PSYCHOTIC_DIS CC_PTSD CC_SCHIZOPHRENIA CC_SUD CC_SUD_C CC_SUI_C CC_TOUR_C 
							CC_TRAUMA_C MOOD_DIS_FLAG OTH_MH_FLAG SCZ_PSY_FLAG
							NUM_TRT_SRVC_EMERG_SRVCS NUM_TRT_SRVC_INPT_CARE NUM_TRT_SRVC_MAT NUM_TRT_SRVC_MH_RX NUM_TRT_SRVC_OT NUM_TRT_SRVC_RES NUM_TRT_SRVC_IOP NUM_TRT_SRVC_TH
							TRT_SRVC_EMERG_SRVCS TRT_SRVC_INPT_CARE TRT_SRVC_MAT TRT_SRVC_MH_RX TRT_SRVC_OT TRT_SRVC_RES TRT_SRVC_IOP TRT_SRVC_TH;
%let replace_miss_vars 	= 	num_ip_stays any_ip_stay tot_ip_stays_los min_ip_stay_los max_ip_stay_los avg_ip_stay_los any_readm_30day_stay
						 	num_nf_stays any_nf_stay tot_nf_stays_los min_nf_stay_los max_nf_stay_los avg_nf_stay_los
						 	num_ed_visits any_ed_visit tot_ed_visits_los min_ed_visit_los max_ed_visit_los avg_ed_visit_los num_ed_to_ip_stay any_ed_to_ip_stay
						 	&comorbid_flags. AVG_CMR_INDEX_READMISSION MIN_CMR_INDEX_READMISSION MAX_CMR_INDEX_READMISSION MEDIAN_CMR_INDEX_READMISSION AVG_CMR_INDEX_MORTALITY
							MIN_CMR_INDEX_MORTALITY MAX_CMR_INDEX_MORTALITY MEDIAN_CMR_INDEX_MORTALITY
						 	&ccw_flags.
						 	&bh_flags.
						 	num_ot_encounters;

/* Includes */
%include "&run_dir./00_include_macros/00_include_formats.sas";
%include "&run_dir./00_include_macros/00_include_macros.sas";

/* Main macro */
%macro create_bene_analytic_file(year);

	/* Set up log/lst/html output files */
	%let datestamp = %sysfunc(date(),yymmddn8.);
	%let logfile = &run_dir./02_log_lst/Analytic_File/&year./01_Create_Bene_Analytic_File_&datestamp..log;
	%let lstfile = &run_dir./02_log_lst/Analytic_File/&year./01_Create_Bene_Analytic_File_&datestamp..lst;
	%let htmlfile = &run_dir./02_log_lst/Analytic_File/&year./01_Create_Bene_Analytic_File_&datestamp..html;
	proc printto 
	    log="&logfile." 
	    print="&lstfile." new; 
	run;
	ods html body="&htmlfile." style=HTMLBlue;

	/* Set year specific libraries */
	%let in00 = &run_dir./00_lookup_tables;
	libname in01 "&run_dir./03_data/01_CmnMod_Extract_Data/&year."; 
	libname in02 "&run_dir./03_data/02_IP_Stay/&year."; 
	libname in03 "&run_dir./03_data/03_NF_Stay/&year."; 
	libname in04 "&run_dir./03_data/04_ED_Visit/&year."; 
	libname in05 "&run_dir./03_data/05_Comorbidities/&year."; 
	libname in06 "&run_dir./03_data/06_CCW/&year."; 
	libname in07 "&run_dir./03_data/07_BH"; 
	libname in08 "&run_dir./03_data/08_Exp/&year.";
	libname out "&run_dir./03_data/Analytic_File/&year.";

	/*****************************************************/
	/* AGGREGATION TO BENE LEVEL						 */
	/*****************************************************/
	/* Aggregate 01 (claims) module measures */
	%macro agg_claims_mod;	

		%macro clm_to_bene(var, var_abbr);

			* read in each claim module file type and transpose of interest variable;
			%let ds = ip lt ot rx;
			%do x = 1 %to %sysfunc(countw(&ds.));
				%let d = %scan(&ds., &x.);
				%if &d. ne rx or (&d. = rx and &var_abbr. ne sptc and &var_abbr. ne sptyc) %then %do;
					proc contents data = in01.&d._hdr_cmn_vars_&year. (keep = &var._: /*%if &year. = 2017 and &var. = ndc %then %do; drop = ndc_label_: ndc_prod_: %end;*/) out = &d._&var._contents (keep = name) noprint;
					run;
					proc sql noprint;
						select count(*) into :max_val separated by ""
						from &d._&var._contents;
					quit;
					data &d._&var_abbr._&year.;
						set in01.&d._hdr_cmn_vars_&year. /*(obs=100000)*/;
						mtm_&var. = 0;
						%do i = 1 %to &max_val.;
							if &var._&i. ne "M" and not missing(&var._&i.) then mtm_&var. = 1;
						%end;
						if mtm_&var. = 0 then delete;
						keep file msis_id submtg_state_cd clm_id &var._: mtm_&var.;
						%if &year. = 2017 %then %do;
							drop ndc_label_: ndc_prod_:;
						%end;
					run;
					proc sort data = &d._&var_abbr._&year.;
						by file submtg_state_cd msis_id clm_id;
					run;
					proc transpose data = &d._&var_abbr._&year. out = &d._&var_abbr._t_&year. (rename = (col1 = &var.) drop = _NAME_ where = (not missing(&var.) and &var. ne "M"));
						by file submtg_state_cd msis_id clm_id;
						var &var._1 - &var._&max_val.;
					run;
					proc datasets lib = permwork nolist;
						delete &d._&var_abbr._&year. &d._&var._contents;
					run;
				%end;
			%end;

			* append all transposed file types;
			%if &d. ne rx or (&d. = rx and &var_abbr. ne sptc and &var_abbr. ne sptyc) %then %do;
				data &var_abbr._&year.;
					set ip_&var_abbr._t_&year. lt_&var_abbr._t_&year. ot_&var_abbr._t_&year. rx_&var_abbr._t_&year.;
				run;
				proc datasets lib = permwork nolist;
					delete ip_&var_abbr._t_&year. lt_&var_abbr._t_&year. ot_&var_abbr._t_&year. rx_&var_abbr._t_&year.;
				run;
			%end;
			%else %do;
				data &var_abbr._&year.;
					set ip_&var_abbr._t_&year. lt_&var_abbr._t_&year. ot_&var_abbr._t_&year.;
				run;
				proc datasets lib = permwork nolist;
					delete ip_&var_abbr._t_&year. lt_&var_abbr._t_&year. ot_&var_abbr._t_&year.;
				run;
			%end;

			* for srvc_prvdr_txnmy_cd, merge on groupings and dedup by those categories;
			%if &var. = srvc_prvdr_txnmy_cd %then %do;
				proc import datafile = "&in00./&var._lookup.xlsx" out = &var_abbr._lookup
					dbms = xlsx
					replace;
					sheet = "&var._lookup";
				run;
				title "Number of Rows Per SRVC_PRVDR_TXNMY_CD in Lookup, &year.";
				title2 "There Should Only Be 1";
				proc sql;
					select &var_abbr._rows, count(*) as num_of_&var_abbr.
					from 
						(select code, count(*) as &var_abbr._rows
						 from &var_abbr._lookup
						 group by code)
					group by &var_abbr._rows;
				quit;
				proc sql;
					create table &var_abbr._wcodes_&year. as
					select 	a.*,
							b.&var_abbr._grp,
							b.&var_abbr._class,
							case
								when missing(b.code) then "SPTC ONLY IN CLAIMS"
								else "MERGED"
							end as _merge
					from &var_abbr._&year. as a
					left join
					&var_abbr._lookup as b
					on a.srvc_prvdr_txnmy_cd = b.code;
				quit;
				title "Frequency of Merge Between Claims SRVC_PRVDR_TXNMY_CD and SRVC_PRVDR_TXNMY_CD Lookup File, &year.";
				proc freq data = &var_abbr._wcodes_&year.;
					table _merge / list missing;
				run;
				%let codes = grp class;
				%do i = 1 %to %sysfunc(countw(&codes.));
					%let code = %scan(&codes., &i.);
					proc sort data = &var_abbr._wcodes_&year. (where = (not missing(&var_abbr._&code.))) out = &var_abbr._&code._&year. (keep = file submtg_state_cd msis_id clm_id &var_abbr._&code.) nodupkey;
						by file submtg_state_cd msis_id clm_id &var_abbr._&code.;
					run;
				%end;
				proc datasets lib = permwork nolist;
					delete &var_abbr._lookup &var_abbr._wcodes_&year. &var_abbr._&year.;
				run;
			%end;

			* for ndc, merge on HEDIS categorizations and dedup by those categorizations;
			%if &var. = ndc %then %do;
				proc import datafile = "&in00./HEDIS_&year..xlsx" out = hedis_&year._lookup
					dbms = xlsx
					replace;
					sheet = "Medications List to NDC Codes";
				run;
				proc import datafile = "&in00./ML_Code_Lookup.xlsx" out = ml_code_lookup
					dbms = xlsx
					replace;
					sheet = "ML_Code_Lookup";
				run;
				proc sql;
					create table hedis_&year._lookup2 as 
					select 	 a.ndc_code
							,b.ndc_ml_code
					from hedis_&year._lookup as a
					left join 
					ml_code_lookup as b
					on a.medication_list = b.medication_list;
				quit;
				title "Number of Medication Lists Associated with Each NDC in HEDIS_&year.";
				title2 "There Can Be More Than One Because an NDC Can Fall Into More Than One HEDIS Categorization";
				proc sql;
					select ndc_rows, count(*) as num_of_ndcs_in_cat
					from 
						(select ndc_code, count(*) as ndc_rows
						 from hedis_&year._lookup2
						 group by ndc_code)
					group by ndc_rows;
				quit;

				proc sql;
					create table &var_abbr._wcodes_&year. as
					select 	a.*,
							b.ndc_ml_code,
							case
								when missing(b.ndc_code) then "NDC ONLY IN CLAIMS"
								else "MERGED"
							end as _merge
					from &var_abbr._&year. as a
					left join
					hedis_&year._lookup2 as b
					on a.ndc = b.ndc_code;
				quit;
				title "Frequency of Merge Between Claims NDC and NDC_CODE Lookup File, &year.";
				proc freq data = &var_abbr._wcodes_&year.;
					table _merge / list missing;
				run;
				proc sort data = &var_abbr._wcodes_&year. (where = (not missing(&var._ml_code))) out = &var_abbr._ml_code_&year. (keep = file submtg_state_cd msis_id clm_id &var._ml_code) nodupkey;
					by file submtg_state_cd msis_id clm_id &var._ml_code;
				run;
				proc datasets lib = permwork nolist;
					delete hedis_&year._lookup: ml_code_lookup &var_abbr._&year. &var_abbr._wcodes_&year.;
				run;
			%end;
		
			* create claim counts and flags for each categorization;
			%macro create_cnts_flgs(var2, var_abbr2);

				proc sort data = &var_abbr2._&year. out = &var_abbr2.2_&year. nodupkey;
					by file submtg_state_cd msis_id clm_id &var2.;
				run; * there should not be any duplicates;
				proc datasets lib = permwork nolist;
					delete &var_abbr2._&year.;
				run;
				data &var_abbr2.3_&year.;
					set &var_abbr2.2_&year.;
					num_clms = 1;
				run;
				proc datasets lib = permwork nolist;
					delete &var_abbr2.2_&year.;
				run;
				proc means data = &var_abbr2.3_&year. nway missing noprint;
					class msis_id submtg_state_cd &var2.;
					output out = &var_abbr2.4_&year. (drop = _TYPE_ _FREQ_) sum(num_clms)=;
				run;
				proc datasets lib = permwork nolist;
					delete &var_abbr2.3_&year.;
				run;
				proc sql noprint;
					select distinct &var2. into :values separated by " "
					from &var_abbr2.4_&year.;
				quit;
				data &var_abbr2.5_&year.;
					set &var_abbr2.4_&year.;
					%do i = 1 %to %sysfunc(countw(&values.));
						%let value = %scan(&values., &i.);
						if &var2. = "&value." then do;
							num_clms_&var_abbr2._&value. = num_clms;
							any_clms_&var_abbr2._&value. = (num_clms_&var_abbr2._&value. > 0);
						end;
					%end;
				run;
				proc datasets lib = permwork nolist;
					delete &var_abbr2.4_&year.;
				run;
				proc sql;
					create table &var_abbr2.6_&year. as
					select 	 msis_id
							,submtg_state_cd
							%do i = 1 %to %sysfunc(countw(&values.));
								%let value = %scan(&values., &i.);
								,max(num_clms_&var_abbr2._&value.) as num_clms_&var_abbr2._&value. label = "Number of Claims With &var2. = &value."
								,max(any_clms_&var_abbr2._&value.) as any_clms_&var_abbr2._&value. label = "Any Claims With &var2. = &value."
							%end;
					from &var_abbr2.5_&year.
					group by msis_id, submtg_state_cd;
				quit;
				proc datasets lib = permwork nolist;
					delete &var_abbr2.5_&year.;
				run;
				data &var_abbr2.7_&year.;
					set &var_abbr2.6_&year.;
					%do i = 1 %to %sysfunc(countw(&values.));
						%let value = %scan(&values., &i.);
						if missing(num_clms_&var_abbr2._&value.) then num_clms_&var_abbr2._&value. = 0;
						if missing(any_clms_&var_abbr2._&value.) then any_clms_&var_abbr2._&value. = 0;
					%end;
				run;
				proc datasets lib = permwork nolist;
					delete &var_abbr2.6_&year.;
				run;

			%mend create_cnts_flgs;
			%if &var_abbr. = sptc %then %do;
				%create_cnts_flgs(var2=&var_abbr._grp, var_abbr2=&var_abbr._grp);
				%create_cnts_flgs(var2=&var_abbr._class, var_abbr2=&var_abbr._class);
			%end;
			%else %if &var_abbr. = ndc %then %do;
				%create_cnts_flgs(var2=&var._ml_code, var_abbr2=&var_abbr._ml_code);
			%end;
			%else %do;
				%create_cnts_flgs(var2=&var., var_abbr2=&var_abbr.);
			%end;

		%mend clm_to_bene;
		%clm_to_bene(var=srvc_prvdr_txnmy_cd, var_abbr=sptc);
		%clm_to_bene(var=srvc_prvdr_type_cd, var_abbr=sptyc);
		%clm_to_bene(var=tos_cd, var_abbr=tos);
		%clm_to_bene(var=bnft_type_cd, var_abbr=btc);
		%clm_to_bene(var=ndc, var_abbr=ndc);

	%mend agg_claims_mod;
	%agg_claims_mod;

	* merge individual variable datasets back together;
	data _01_agg_&year.;
		merge sptc_grp7_&year. (in=sptc_grp) sptc_class7_&year. (in=sptc_class) sptyc7_&year. (in=sptyc) tos7_&year. (in=tos) btc7_&year. (in=btc) ndc_ml_code7_&year. (in=ndc);
		by msis_id submtg_state_cd;
		in_sptc_grp = sptc_grp;
		in_sptc_class = sptc_class;
		in_sptyc = sptyc;
		in_tos = tos;
		in_btc = btc;
		in_ndc = ndc;
	run;
	title "Merge of Bene-Level Variable Datasets, &year.";
	title2 "Some Benes Only Have Certain Variables";
	proc freq data = _01_agg_&year.;
		table in_sptc_grp * in_sptc_class * in_sptyc * in_tos * in_btc * in_ndc / list missing;
	run;
	proc datasets lib = permwork nolist;
		delete sptc_grp7_&year. sptc_class7_&year. sptyc7_&year. tos7_&year. btc7_&year. ndc_ml_code7_&year.;
	run;

	/* For OT claims, find unique encounters and add on to _01_agg_&year. file */
	data ot_encounters_&year.;
		set in01.ip_hdr_cmn_vars_&year. (/*obs=100000*/ keep = msis_id submtg_state_cd srvc_dt ed_visit
										 where = (ed_visit = 1 and not missing(srvc_dt)))
  			in01.ot_hdr_cmn_vars_&year. (/*obs=100000*/ keep = msis_id submtg_state_cd srvc_dt
										 where = (not missing(srvc_dt)));
	run;
	proc sort data = ot_encounters_&year. (keep = msis_id submtg_state_cd srvc_dt) nodupkey;
		by msis_id submtg_state_cd srvc_dt;
	run;
	proc sql;
		create table ot_encounters2_&year. as
		select 	msis_id,
				submtg_state_cd,
				count(*) as num_ot_encounters label = "Number of Unique OT Encounters by Srvc_Dt"
		from ot_encounters_&year.
		group by msis_id, submtg_state_cd;
	quit;
	data  _01_agg2_&year.;
		merge _01_agg_&year. (in=a) ot_encounters2_&year. (in=b);
		by msis_id submtg_state_cd;
	run; 
	proc datasets lib = permwork nolist;
		delete ot_encounters_&year. ot_encounters2_&year. _01_agg_&year.;
	run;

	/* Aggregate 02, 03, 04 (stay/visit) module measures */
	%macro agg_stays(d, mod);
		title "0&mod. Module Aggregation, &year.";
		%let d_up = %upcase(&d.);
		proc sort data = in0&mod..&d._final_&year. out = &d._final_&year. (keep = msis_id submtg_state_cd &d._id &d._bgn_dt &d._end_dt &d._los 
																				 %if &d. = ip_stay %then %do; readm_30day_stay %end;
																				 %if &d. = ed_visit %then %do; ed_to_ip_stay %end;) 
			nodupkey;
			by msis_id submtg_state_cd &d._id &d._los 
			   %if &d. = ip_stay %then %do; readm_30day_stay %end;
			   %if &d. = ed_visit %then %do; ed_to_ip_stay %end;
			;
		run;
		title2 "Ensure Only One Entry Per &d_up._ID After De-Dup By Stay/Visit-Level Variables";
		title3 "If More Than 1 Then Investigate";
		proc sql;
			select num_&d._id_rows, count(*) as counter
			from
				(select &d._id, count(*) as num_&d._id_rows
			 	 from &d._final_&year.
			 	 group by &d._id
				 )
			group by num_&d._id_rows;
		quit;

		* update overlapping stays - make into one stay (if one day apart then still separate stays);
		proc sort data = &d._final_&year.;
			by submtg_state_cd msis_id &d._bgn_dt &d._end_dt &d._id;
		run;
		data &d._final2_&year.;
			set &d._final_&year.;
			by submtg_state_cd msis_id;
			retain prev_&d._id prev_&d._end_dt;
			format prev_&d._end_dt date9.;
			if _N_ = 1 then prev_&d._id = 0;

			if first.msis_id then do;
				&d._id2 = prev_&d._id + 1;
				&d._los2 = &d._los;
				prev_&d._id = &d._id2;
				prev_&d._end_dt = &d._end_dt;
			end;
			else do;
				if &d._bgn_dt < prev_&d._end_dt and &d._end_dt < prev_&d._end_dt then do;
					delete;
				end;
				else if &d._bgn_dt <= prev_&d._end_dt and &d._end_dt >= prev_&d._end_dt then do;
					&d._id2 = prev_&d._id;
					&d._los2 = sum(&d._los, intck('day', prev_&d._end_dt, &d._bgn_dt), -1);
					prev_&d._id = &d._id2;
					prev_&d._end_dt = &d._end_dt;
				end;
				else do;
					&d._id2 = prev_&d._id + 1;
					&d._los2 = &d._los;
					prev_&d._id = &d._id2;
					prev_&d._end_dt = &d._end_dt;
				end;
			end;
		run;
		title2 "Check That d._los2 Is Never Less Than 0";
		proc print data = &d._final2_&year. (obs = 5 where = (&d._los2 < 0));
		run;
		title2 "Check of &d_up. (0&mod. Module) Combining Overlapping Stays";
		proc sql outobs = 5;
			create table &d._comb_check_benes as
			select distinct msis_id, submtg_state_cd
			from &d._final2_&year.
			where &d._los ne &d._los2;
		quit;
		title3 "Before Aggregation";
		proc sql;
			select a.*
			from &d._final2_&year. as a
			inner join
			&d._comb_check_benes as b
			on a.msis_id = b.msis_id and a.submtg_state_cd = b.submtg_state_cd
			order by msis_id, submtg_state_cd, &d._bgn_dt, &d._end_dt;
		quit;
		proc sql;
			create table &d._final3_&year. as
			select 	msis_id,
					submtg_state_cd,
					&d._id2,
					min(&d._bgn_dt) as &d._bgn_dt format date9.,
					max(&d._end_dt) as &d._end_dt format date9.,
					sum(&d._los2) as &d._los
					%if &d. = ip_stay %then %do; 
						,max(readm_30day_stay) as readm_30day_stay
					%end;
					%if &d. = ed_visit %then %do; 
						,max(ed_to_ip_stay) as ed_to_ip_stay
					%end;
			from &d._final2_&year.
			group by msis_id, submtg_state_cd, &d._id2;
		quit;
		title3 "After Aggregation";
		proc sql;
			select a.*
			from &d._final3_&year. as a
			inner join
			&d._comb_check_benes as b
			on a.msis_id = b.msis_id and a.submtg_state_cd = b.submtg_state_cd
			order by msis_id, submtg_state_cd, &d._bgn_dt, &d._end_dt;
		quit;
		title2 "Ensure Only One Entry Per &d_up._ID After Combining Overlapping Stays";
		title3 "If More Than 1 Then Investigate";
		proc sql;
			select num_&d._id_rows, count(*) as counter
			from
				(select &d._id2, count(*) as num_&d._id_rows
			 	 from &d._final3_&year.
			 	 group by &d._id2
				 )
			group by num_&d._id_rows;
		quit;

		proc sql;
			create table _0&mod._agg_&year. as
			select 	msis_id,
					submtg_state_cd,
					&year. as year label = "Calendar Year",
					count(distinct &d._id2) as num_&d.s label = "Number of &d_up.S",
					1 as any_&d. label = "Any &d_up.",
					sum(&d._los) as tot_&d.s_los label = "Total Length of &d_up.S",
					min(&d._los) as min_&d._los label = "Minimum Length of &d_up.",
					max(&d._los) as max_&d._los label = "Maximum Length of &d_up.",
					calculated tot_&d.s_los / calculated num_&d.s as avg_&d._los label = "Average Length of &d_up."
					%if &d. = ip_stay %then %do; 
						,max(readm_30day_stay) as any_readm_30day_stay label = "Any Readmissions Within 30 Days of Any &d_up."
					%end;
					%if &d. = ed_visit %then %do; 
						,sum(ed_to_ip_stay) as num_ed_to_ip_stay label = "Number of ED Visits to IP Stays"
						,max(ed_to_ip_stay) as any_ed_to_ip_stay label = "Any ED Visits to IP Stays"
					%end;
			from &d._final3_&year.
			group by msis_id, submtg_state_cd;
		quit; 

		title2 "Check of &d_up. (0&mod. Module) Stay to Bene Level Aggregation";
		proc sql outobs = 5;
			create table &d._agg_check_benes as
			select distinct msis_id, submtg_state_cd
			from in0&mod..&d._final_&year.;
		quit;
		title3 "Before Aggregation";
		proc sql;
			select a.*
			from &d._final3_&year. as a
			inner join
			&d._agg_check_benes as b
			on a.msis_id = b.msis_id and a.submtg_state_cd = b.submtg_state_cd
			order by msis_id, submtg_state_cd, &d._bgn_dt, &d._end_dt;
		quit;
		title3 "After Aggregation";
		proc sql;
			select a.*
			from _0&mod._agg_&year. as a
			inner join
			&d._agg_check_benes as b
			on a.msis_id = b.msis_id and a.submtg_state_cd = b.submtg_state_cd
			order msis_id, submtg_state_cd;
		quit;

		title2 "Check of &d_up. (0&mod. Module) Stay to Bene Level Aggregation For Benes With Very High LOS";
		proc sql outobs = 5;
			create table &d._agg_check_benes2 as
			select distinct msis_id, submtg_state_cd
			from _0&mod._agg_&year. (where = (tot_&d.s_los > 365));
		quit;
		title3 "Before Aggregation";
		proc sql;
			select a.*
			from &d._final3_&year. as a
			inner join
			&d._agg_check_benes2 as b
			on a.msis_id = b.msis_id and a.submtg_state_cd = b.submtg_state_cd
			order by msis_id, submtg_state_cd, &d._bgn_dt, &d._end_dt;
		quit;
		title3 "After Aggregation";
		proc sql;
			select a.*
			from _0&mod._agg_&year. as a
			inner join
			&d._agg_check_benes2 as b
			on a.msis_id = b.msis_id and a.submtg_state_cd = b.submtg_state_cd
			order by msis_id, submtg_state_cd;
		quit;
		proc datasets lib = permwork nolist;
			delete &d._final: &d._comb_check_benes: &d._agg_check_benes:;
		run;
	%mend agg_stays;
	%agg_stays(d=ip_stay, mod=2);
	%agg_stays(d=nf_stay, mod=3);
	%agg_stays(d=ed_visit, mod=4);

	/* Aggregate 05 (comorbidities) module measures */
	proc sql;
		create table _05_agg_&year. as
		select	 msis_id
				,submtg_state_cd
				%do j = 1 %to %sysfunc(countw(&comorbid_flags.));
					%let flag = %scan(&comorbid_flags., &j.);
					,max(&flag.) as &flag.
				%end; 
				,mean(CMR_INDEX_READMISSION) as AVG_CMR_INDEX_READMISSION label = "Average CMR_INDEX_READMISSION"
				,min(CMR_INDEX_READMISSION) as MIN_CMR_INDEX_READMISSION label = "Minimum CMR_INDEX_READMISSION"
				,max(CMR_INDEX_READMISSION) as MAX_CMR_INDEX_READMISSION label = "Maximum CMR_INDEX_READMISSION"
				,median(CMR_INDEX_READMISSION) as MEDIAN_CMR_INDEX_READMISSION label = "Median CMR_INDEX_READMISSION"
				,mean(CMR_INDEX_MORTALITY) as AVG_CMR_INDEX_MORTALITY label = "Average CMR_INDEX_MORTALITY"
				,min(CMR_INDEX_MORTALITY) as MIN_CMR_INDEX_MORTALITY label = "Minimum CMR_INDEX_MORTALITY"
				,max(CMR_INDEX_MORTALITY) as MAX_CMR_INDEX_MORTALITY label = "Maximum CMR_INDEX_MORTALITY"
				,median(CMR_INDEX_MORTALITY) as MEDIAN_CMR_INDEX_MORTALITY label = "Median CMR_INDEX_MORTALITY"
		from in05.comorbidty_all_clms_fnl_hdr_&year.
		group by msis_id, submtg_state_cd;
	quit;
	title "05 Module Aggregation, &year.";
	title2 "Check of COMORBIDITY (05 Module) Aggregation";
	proc sql outobs = 5;
		create table comorb_agg_check_benes as
		select distinct msis_id, submtg_state_cd
		from in05.comorbidty_all_clms_fnl_hdr_&year.;
	quit;
	title3 "Before Aggregation";
	proc sql;
		select a.*
		from in05.comorbidty_all_clms_fnl_hdr_&year. as a
		inner join
		comorb_agg_check_benes as b
		on a.msis_id = b.msis_id and a.submtg_state_cd = b.submtg_state_cd
		order by msis_id, submtg_state_cd;
	quit;
	title3 "After Aggregation";
	proc sql;
		select a.*
		from _05_agg_&year. as a
		inner join
		comorb_agg_check_benes as b
		on a.msis_id = b.msis_id and a.submtg_state_cd = b.submtg_state_cd
		order by msis_id, submtg_state_cd;
	quit;
	data _05_agg_&year.;
		set _05_agg_&year.;
		label
			CMR_DEPRESS = 'Depression comorbidity'
			CMR_LUNG_CHRONIC = 'Chronic pulmonary disease�comorbidity'
			CMR_NEURO_SEIZ = 'Seizures and epilepsy�comorbidity'
			CMR_OBESE = 'Obesity�comorbidity'
			CMR_PARALYSIS = 'Paralysis�comorbidity'
			CMR_RENLFL_SEV = 'Renal failure, severe�comorbidity'
			CMR_AIDS = 'Acquired immune deficiency syndrome�comorbidity'
			CMR_ALCOHOL = 'Alcohol abuse�comorbidity'
			CMR_ANEMDEF = 'Deficiency anemias�comorbidity'
			CMR_AUTOIMMUNE = 'Autoimmune conditions comorbidity'
			CMR_BLDLOSS = 'Chronic blood loss anemia comorbidity�'
			CMR_CANCER_LEUK = 'Leukemia�comorbidity'
			CMR_CANCER_LYMPH = 'Lymphoma�comorbidity'
			CMR_CANCER_METS = 'Metastatic cancer�comorbidity'
			CMR_CANCER_NSITU = 'Solid tumor without metastasis, in situ�comorbidity'
			CMR_CANCER_SOLID = 'Solid tumor without metastasis, malignant�comorbidity'
			CMR_CBVD = 'Cerebrovascular disease comorbidity'
			CMR_COAG = 'Coagulopathy�comorbidity'
			CMR_DEMENTIA = 'Dementia�comorbidity'
			CMR_DIAB_CX = 'Diabetes with chronic complications�comorbidity'
			CMR_DIAB_UNCX = 'Diabetes without chronic complications�comorbidity'
			CMR_DRUG_ABUSE = 'Drug abuse�comorbidity'
			CMR_HF = 'Heart failure�comorbidity'
			CMR_HTN_CX = 'Hypertension, complicated�comorbidity'
			CMR_HTN_UNCX = 'Hypertension, uncomplicated�comorbidity'
			CMR_LIVER_MLD = 'Liver disease, mild�comorbidity'
			CMR_LIVER_SEV = 'Liver disease, moderate to severe�comorbidity'
			CMR_NEURO_MOVT = 'Neurological disorders affecting movement�comorbidity'
			CMR_NEURO_OTH = 'Other neurological disorders comorbidity'
			CMR_PERIVASC = 'Peripheral vascular disease comorbidity�'
			CMR_PSYCHOSES = 'Psychoses�comorbidity'
			CMR_PULMCIRC = 'Pulmonary circulation disease�comorbidity'
			CMR_RENLFL_MOD = 'Renal failure, moderate�comorbidity'
			CMR_THYROID_HYPO = 'Hypothyroidism�comorbidity'
			CMR_THYROID_OTH = 'Other thyroid disorders comorbidity'
			CMR_ULCER_PEPTIC = 'Peptic ulcer with bleeding�comorbidity'
			CMR_VALVE = 'Valvular disease�comorbidity'
			CMR_WGHTLOSS = 'Weight loss�comorbidity'
			;
	run;
	proc datasets lib = permwork nolist;
		delete comorb_agg_check_benes;
	run;

	/* Label 06 (CCW) and 07 (BH) module measures */
	data _06_agg_&year.;
		set in06.ccw_&year.;
		label
			BENE_ANEMIA = 'CCW Anemia�beneficiary flag'
			BENE_ASTHMA = 'CCW Asthma�beneficiary flag'
			BENE_CKD = 'CCW Chronic Kidney Disease�beneficiary flag'
			BENE_HYPERT = 'CCW Hypertension�beneficiary flag'
			BENE_AUTISM = 'CCW Autism Spectrum Disorders�beneficiary flag'
			BENE_CERPAL = 'CCW Cerebral Palsy�beneficiary flag'
			BENE_CYSFIB = 'CCW Cystic Fibrosis and Other Metabolic Developmental Disorders�beneficiary flag'
			BENE_EPILEP = 'CCW Epilepsy�beneficiary flag'
			BENE_INTDIS = 'CCW Intellectual Disabilities and Related Conditions�beneficiary flag'
			BENE_LEADIS = 'CCW Learning Disabilities�beneficiary flag'
			BENE_MIGRAINE = 'CCW Migraine and Chronic Headache�beneficiary flag'
			BENE_MOBIMP = 'CCW Mobility Impairments�beneficiary flag'
			BENE_MUSDYS = 'CCW Muscular Dystrophy�beneficiary flag'
			BENE_OBESITY = 'CCW Obesity�beneficiary flag'
			BENE_OTHDEPL = 'CCW Other Developmental Delays beneficiary flag'
			BENE_SPIBIF = 'CCW Spina Bifida and Other Congenital Anomalies of the Nervous System beneficiary flag'
			BENE_CAT = 'CCW Cataract�beneficiary flag'
			BENE_AMI = 'CCW Acute Myocardial Infarction�beneficiary flag'
			BENE_STRK = 'CCW Stroke/Transient Ischemic Attack�beneficiary flag'
			BENE_AFIB = 'CCW Atrial Fibrillation�beneficiary flag'
			BENE_HFRAC = 'CCW Hip/Pelvic Fracture�beneficiary flag'
			BENE_CHF = 'CCW Heart Failure�beneficiary flag'
			BENE_CRC = 'CCW Colorectal Cancer�beneficiary flag'
			BENE_ENDC = 'CCW Endometrial Cancer�beneficiary flag'
			BENE_BRC = 'CCW Female/Male Breast Cancer�beneficiary flag'
			BENE_LNGC = 'CCW Lung Cancer�beneficiary flag'
			BENE_PRC = 'CCW Prostate Cancer�beneficiary flag'
			BENE_HYPOTH = 'CCW Acquired Hypothyroidism�beneficiary flag'
			BENE_HYPERP = 'CCW Benign Prostatic Hyperplasia�beneficiary flag'
			BENE_COPD = 'CCW Chronic Obstructive Pulmonary Disease and Bronchiectasis�beneficiary flag'
			BENE_DIAB = 'CCW Diabetes�beneficiary flag'
			BENE_HYPERL = 'CCW Hyperlipidemia�beneficiary flag'
			BENE_OST = 'CCW Osteoporosis�beneficiary flag'
			BENE_ALZ = "CCW Alzheimer's Disease�beneficiary flag"
			BENE_ALZRDSD = "CCW Alzheimer's Disease and Related Disorders or Senile Dementia�beneficiary flag"
			BENE_IHD = 'CCW Ischemic Heart Disease�beneficiary flag'
			BENE_RAOA = 'CCW Rheumatoid Arthritis/ Osteoarthritis (RA/OA)�beneficiary flag'
			BENE_GLCM = 'CCW Glaucoma�beneficiary flag'
			BENE_FIBRO = 'CCW Fibromyalgia and Chronic Pain and Fatigue�beneficiary flag'
			BENE_HIVAIDS = 'CCW Human Immunodeficiency Virus and/or Acquired Immunodeficiency Syndrome (HIV/AIDS)�beneficiary flag'
			BENE_LEUKLYMP = 'CCW Leukemias and Lymphomas�beneficiary flag'
			BENE_PVD = 'CCW Peripheral Vascular Disease (PVD) beneficiary flag'
			BENE_ULCERS = 'CCW Pressure and Chronic Ulcers beneficiary flag'
			BENE_HEPVIRAL = 'CCW Viral Hepatitis (General), beneficiary flag'
			BENE_LIVER = 'CCW Liver Disease, Cirrhosis, and Other Liver Conditions (except Viral Hepatitis)�beneficiary flag'
			BENE_MULSCL = 'CCW Multiple Sclerosis and Transverse Myelitis�beneficiary flag'
			BENE_VISUAL = 'CCW Sensory � Blindness and Visual Impairment beneficiary flag'
			BENE_HEARIM = 'CCW Sensory � Deafness and Hearing Impairment beneficiary flag'
			BENE_SPINJ = 'CCW Spinal Cord Injury beneficiary flag'
			BENE_BRAINJ = 'CCW Traumatic Brain Injury and Nonpsychotic Mental Disorders due to Brain Damage beneficiary flag'
			BENE_SKLC = 'CCW Sickle Cell Disease beneficiary flag'
			BENE_TUD = 'CCW Tobacco use disorders beneficiary flag'
			;
	run;
	proc sort data = _06_agg_&year.;
		by msis_id submtg_state_cd;
	run;

	data _07_agg_&year.;
		set in07._07_200_bene_flags;
		label
			ANXIETY_DIS_FLAG = 'Anxiety disorders category beneficiary flag'
			CC_ADHD = 'Attention deficit hyperactivity disorder beneficiary flag'
			CC_ADHD_C = 'ADHD in children'
			CC_ANXIETY = 'Anxiety disorders beneficiary flag'
			CC_ANXIETY_C = 'Anxiety in children'
			CC_AUD = 'Alcohol use disorders beneficiary flag'
			CC_BEHAV_C = 'Behavior/conduct disorders in children '
			CC_BH = 'Behavioral health conditions category beneficiary flag'
			CC_BIPOLAR_DIS = 'Bipolar disorder beneficiary flag'
			CC_DEPR_C = 'Depressive disorders in children'
			CC_DEPRESSION = 'Depression beneficiary flag'
			CC_DEPRESSIVE_DIS = 'Depressive disorders beneficiary flag'
			CC_DUD = 'Drug use disorders beneficiary flag'
			CC_MH = 'Mental health conditions category beneficiary flag'
			CC_MOOD_C = 'Mood disorders in children'
			CC_OMH_C = 'Other mental health disorders in children'
			CC_OTHER_MH = 'Other mental health disorders beneficiary flag'
			CC_OUD = 'Opioid use disorder beneficiary flag'
			CC_PERSONALITY_DIS = 'Personality disorders beneficiary flag'
			CC_PSY_C = 'Psychotic disorders in children'
			CC_PSYCHOTIC_DIS = 'Psychotic disorder beneficiary flag'
			CC_PTSD = 'Post-Traumatic Stress Disorder beneficiary flag'
			CC_SCHIZOPHRENIA = 'Schizophrenia and other psychotic disorders beneficiary flag'
			CC_SUD = 'Substance use disorders category beneficiary flag'
			CC_SUD_C = 'SUD in children'
			CC_SUI_C = 'Suicidal ideation in children'
			CC_TOUR_C = 'Tourette�s/tic disorders in children '
			CC_TRAUMA_C = 'Trauma/stressor-related disorders in children'
			MOOD_DIS_FLAG = 'Mood disorder category beneficiary flag'
			OTH_MH_FLAG = 'Other mental health conditions category beneficiary flag'
			SCZ_PSY_FLAG = 'Schizophrenia and other psychotic disorders category beneficiary flag'
			TRT_SRVC_EMERG_SRVCS = 'Any Claim With Emergency Services Service Flag'
			TRT_SRVC_INPT_CARE = 'Any Claim With Inpatient Care Service Flag'
			TRT_SRVC_MAT = 'Any Claim With Mat Service Flag'
			TRT_SRVC_MH_RX = 'Any Claim With Mental Health Medication Service Flag'
			TRT_SRVC_OT = 'Any Claim With Outpatient Service Flag'
			TRT_SRVC_RES = 'Any Claim With Residential Service Flag'
			TRT_SRVC_IOP = 'Any Claim With Intensive Outpatient Or Partial Hospitalization Service Flag'
			TRT_SRVC_TH = 'Any Claim With Telehealth Service Flag'
			NUM_TRT_SRVC_EMERG_SRVCS = 'Number of Claims With Emergency Services Service Flag'
			NUM_TRT_SRVC_INPT_CARE = 'Number of Claims With Inpatient Care Service Flag'
			NUM_TRT_SRVC_MAT = 'Number of Claims With Mat Service Flag'
			NUM_TRT_SRVC_MH_RX = 'Number of Claims With Mental Health Medication Service Flag'
			NUM_TRT_SRVC_OT = 'Number of Claims With Outpatient Service Flag'
			NUM_TRT_SRVC_RES = 'Number of Claims With Residential Service Flag'
			NUM_TRT_SRVC_IOP = 'Number of Claims With Intensive Outpatient Or Partial Hospitalization Service Flag'
			NUM_TRT_SRVC_TH = 'Number of Claims With Telehealth Service Flag';
	run;
	proc sort data = _07_agg_&year.;
		by msis_id submtg_state_cd;
	run;

	/*****************************************************/
	/* COMBINE BENE LEVEL FILES						     */
	/******************************************************/
	* sort enrollment data;
	proc sort data = in01.enrollment_&year. out = enrollment_&year.;
		by msis_id submtg_state_cd;
	run;

	* add num_clms and any_clms variables from _01 module to list of variables that need to be replaced with 0 if missing;
	proc contents data = _01_agg2_&year. (keep = num_clms_: any_clms_:) out = _01_agg2_contents_&year. (keep = name) noprint;
	run;
	proc sql noprint;
		select distinct name into :also_replace_miss separated by " "
		from _01_agg2_contents_&year.;
	quit;
	%let replace_miss_vars = &replace_miss_vars. &also_replace_miss.; 

	* combine all bene-level datasets;
	data out.analytic_file_&year.;
		merge enrollment_&year. (in=enroll keep=&enroll_vars.)  _01_agg2_&year. (in=clms drop=in_sptc: in_sptyc in_tos in_btc in_ndc)  _02_agg_&year. (in=ipstays)  _03_agg_&year. (in=nfstays)  _04_agg_&year. (in=edvisits)
			  _05_agg_&year. (in=comorbid) _06_agg_&year. (in=ccw keep=msis_id submtg_state_cd &ccw_flags.) _07_agg_&year. (in=bh keep=msis_id submtg_state_cd year &bh_flags. where=(year=&year.));	  
		by msis_id submtg_state_cd;
		in_enroll = enroll;
		in_clms = clms;
		in_ipstays = ipstays;
		in_nfstays = nfstays;
		in_edvisits = edvisits;
		in_comorbid = comorbid;
		in_ccw = ccw;
		in_bh = bh;

		%do i = 1 %to %sysfunc(countw(&replace_miss_vars.));
			%let var = %scan(&replace_miss_vars., &i.);
			if missing(&var.) then &var. = 0;
		%end;
		
		year = &year.;
		label
			in_enroll = "Bene is in enrollment dataset (<18 in age and eligible)"
			in_clms = "Bene is in any claims dataset (ip, lt, ot, and/or rx) and has at least one non-missing value for srvc_prvdr_txnmy_cd, srvcd_prvdr_type_cd, tos_cd, bnft_type_cd, or ndc in any of those claims"
			in_ipstays = "Bene is in IP stays dataset"
			in_nfstays = "Bene is in NF stays dataset"
			in_edvisits = "Bene is in ED visits dataset"
			in_comorbid = "Bene is in commorbidity dataset"
			in_ccw = "Bene is in CCW dataset"
			in_bh = "Bene is in behavioral health dataset"
			msis_id = "Bene identification number that in combination with submtg_state_cd uniquely identifies a bene in a given year"
			submtg_state_cd = "Bene state; used in conjunction with msis_id to identify a unique bene in a given year";
	run;

	* QA;
	options device = actximg;
	ods excel file = "&run_dir./04_qa/Analytic_File/&year./&year._analytic_file_&datestamp..xlsx" options(embedded_titles = 'yes' sheet_interval = 'now' sheet_name = "in_modules");

		* frequency of merge between all modules at the bene-level;
		title "Examine Benes That Do Not Show Up in Some/All Modules, &year.";
		title2 "There Should Not Be Any Benes Not In The Enrollment File (I.e., in_enroll should never be 0)";
		proc freq data = out.analytic_file_&year. order = freq noprint;
			table in_enroll * in_clms * in_ipstays * in_nfstays * in_edvisits * in_comorbid * in_ccw * in_bh / list missing out = freq_out;
		run;
		data freq_out;
			set freq_out;
			if count > 0 and count < 11 then percent = 999;
		run;
		proc print data = freq_out;
			format count lowc. percent pctmask.;
		run;

		* proc means of all benes;
		ods excel options(embedded_titles = 'yes' sheet_interval = 'now' sheet_name = "summary_all_benes");
		title "Summary of Final Bene-Level Analytic File, &year.";
		proc means data = out.analytic_file_&year. (drop = bene_id) nway missing n nmiss min median mean std max STACKODSOUTPUT;
		run;

		* proc means of ssi benes;
		ods excel options(embedded_titles = 'yes' sheet_interval = 'now' sheet_name = "summary_ssi_benes");
		title "Summary of Final Bene-Level Analytic File, &year.";
		title2 "Where SSI_ID = 1";
		proc means data = out.analytic_file_&year. (drop = bene_id where = (ssi_id = 1)) nway missing n nmiss min median mean std max STACKODSOUTPUT;
		run;

		* proc means of non-ssi benes;
		ods excel options(embedded_titles = 'yes' sheet_interval = 'now' sheet_name = "non_ssi_benes");
		title "Summary of Final Bene-Level Analytic File, &year.";
		title2 "Where SSI_ID = 0";
		proc means data = out.analytic_file_&year. (drop = bene_id where = (ssi_id = 0)) nway missing n nmiss min median mean std max STACKODSOUTPUT;
		run;

		* proc means of unknown ssi status benes;
		ods excel options(embedded_titles = 'yes' sheet_interval = 'now' sheet_name = "summary_unknwn_ssi_benes");
		title "Summary of Final Bene-Level Analytic File, &year.";
		title2 "Where SSI_ID = Missing";
		proc means data = out.analytic_file_&year. (drop = bene_id where = (missing(ssi_id))) nway missing n nmiss min median mean std max STACKODSOUTPUT;
		run;

	ods excel close;

	* proc contents;
	title "Contents of Final Bene-Level Analytic File, &year.";
	proc contents data = out.analytic_file_&year.;
	run;

	/* Clean up environment */
	proc datasets lib = permwork nolist;
		delete _01_agg2_&year. _02_agg_&year. _03_agg_&year. _04_agg_&year.
			   _05_agg_&year. _06_agg_&year. _07_agg_&year. enrollment_&year.
			   _01_agg2_contents_&year. freq_out;
	run;

	/* Add program run time to the log */
	data _null_;
		now = datetime();
		dur = datetime() - &timer_start.; *-- doing datetime() twice for most accuracy, probably overkill ;
		put "Timer ending at" now dateampm.;
	    put 30*"-" / " TOTAL DURATION:" dur TIME13.2 / 30*"-";
	run;

	proc printto;
	run;

	ods html close;

%mend create_bene_analytic_file;

*====================================================================*;        
* Calls														 		 *;
*====================================================================*;
%macro run_it;

	%do y = 2017 %to 2019;

		%create_bene_analytic_file(year=&y.);

	%end;

%mend run_it;
%run_it;

